// This do-file produces all tables in the main text of 
// "Financing Competitors: Shadow Banks' Funding and Mortgage Market Competition"

global analysis "financing_competitors_upload/data/"

////////////////////////////////////////////////////////////////////////////////
// Figure 1 
use ${analysis}figure1,clear 
twoway (bar bank_limit Y1 if Bank==2,sort yaxis(1) fcolor(black) fintensity(100) lcolor(black) barwidth(0.2) ytitle("Total Credit Limit (Trillion)",axis(1)) ylabel(,nogrid) ) ///
		(bar bank_limit Y2 if Bank==1,sort yaxis(1) fcolor(gs8) fintensity(100) lcolor(gs8) barwidth(0.2) ytitle("Total Credit Limit (Trillion)",axis(1)) ylabel(,nogrid) ) ///
		(bar bank_limit Y3 if Bank==0,sort yaxis(1) fcolor(gs12) fintensity(100) lcolor(gs12) barwidth(0.2)  ylabel(,nogrid) ), ///
		legend(label(1 "Mortgage Banks") label(2 "Non-Mortgage Banks") label (3 "Others") size(small)  cols(1) region(lc(white)) ring(0) position(11)) ///
		xlabel(2012(1)2017)	xtitle("")   scheme(sj)   ///
		 graphregion(color(white) margin(5 5 5 5)) plotregion(lcolor(black))		 
graph export figure1.png, as(png) replace	

// Figure 2 
use ${analysis}figure2ab,clear
replace loan_type = 5 if loan_type==1 & conforming_loan_limit=="NC"

gen count = 1 
collapse  (sum) count, by(lei loan_type sbank)

egen tot = sum(count), by(lei)
gen share = 100*count/tot 

gen label = "Shadow Bank" if sbank==1
replace label = "Bank" if sban==0

tab loan_type, gen(loan_type)
eststo sbank: reg share loan_type1-loan_type5 if sbank==1, nocons 
eststo bank: reg share loan_type1-loan_type5 if sbank==0, nocons 

coefplot (sbank,   recast(bar) ciopts(recast(rcap) color(black)) citop barwidt(0.3) levels(95)  color(black) mc(black) msize(small) lc(black)  ) /// 
          (bank,   recast(bar) ciopts(recast(rcap) color(black)) citop barwidt(0.3)  levels(95) color(gs8)  mc(gs8) msize(small)  lc(gs8) )  ///
          , vertical ytitle("Application Share") xtitle("") ylabel(,grid gsty(dot)) graphregion(fcolor(white) color(white) lcolor(white)) xlabel(1 "Conforming" 2 "FHA" 3 "VA" 4 "RHS" 5 "Jumbo",grid gsty(dot) ) ///
		  		legend(on order(1 "Shadow Bank" 3 "Bank" ) row(2) ring(0) position(1) ) ///
				title("Loan Product Type",  size(medsmall) ) 
graph export figure2a.png, as(png) replace

use ${analysis}figure2ab,clear
gen age = 20 if applicant_age=="<25"
replace age = 25 if applicant_age=="25-34" 
replace age = 35 if applicant_age=="35-44"
replace age = 45 if applicant_age=="45-54"
replace age = 55 if applicant_age=="55-64"
replace age = 65 if applicant_age=="65-74" 
replace age = 75 if applicant_age==">74"
 
gen count = 1 
collapse (sum) count, by(age lei sbank)

egen tot = sum(count), by(lei)
gen age_share = 100*count/tot

tab age, gen(age)
eststo sbank: reg age_share age1-age7 if sbank==1, nocons
eststo bank: reg age_share age1-age7 if sbank==0, nocons

coefplot (sbank,   ciopts(recast(rarea) col(gs12) )  levels(95) msym(Oh)  mc(black) msize(small) lc(black)  ) /// 
          (bank,   ciopts(recast(rarea) col(gs12) )  levels(95) msym(OH)   mc(black) msize(small)  lc(black) )  ///
          , vertical ytitle("Application Share") xtitle("Applicant Age") ylabel(,grid gsty(dot)) graphregion(fcolor(white) color(white) lcolor(white)) xlabel(1 "20" 2 "25" 3 "35" 4 "45" 5 "55" 6 "65" 7 "75",grid gsty(dot) ) ///
		  		legend(on order(2 "Shadow Bank" 4 "Bank" ) row(2) ring(0) position(1) ) ///
				title("Borrower Age",  size(medsmall) ) 
graph export figure2b.png, as(png) replace

// download loan-level disclosure data from Fannie and Freddie
/*
use GSE_combined,clear
keep if Year>=2011 & Year<=2017
drop if missing(origin_int_rate) | missing(ltv) | missing(dti) | missing(fico)
keep if origin_channel=="R"

replace fico = . if fico==9999
drop if seller == "OTHER" | seller == "Other sellers"

merge m:1 seller using ${rawdata}gse_link
drop if _merge==2
gen sbank = (_merge==3)
drop _merge
*/
use ${analysis}figure2c,clear 
twoway(histogram fico if sbank==0, color(black) )(histogram fico if sbank==1, color(gs8%70) lcolor(gs8%0)), ytitle("Density") xtitle("FICO") ylabel(,grid gsty(dot)) graphregion(fcolor(white) color(white) lcolor(white)) ///
		  		legend(on order(2 "Shadow Bank" 1 "Bank" ) row(2) ring(0) position(11) ) ///
				title("Borrower FICO - GSE",  size(medsmall) ) xlabel(,grid gsty(dot))
graph export figure2c.png, as(png) replace


// download loan-level disclosure data from Ginnie Mae 
/*use ginnie,clear
drop loan_id seller cltv property_type occpancy_type zip product_type Year month msa servicer
gen Year = year(origin_date)
keep if Year>=2011 & Year<=2017
drop if missing(origin_int_rate) | missing(ltv) | missing(dti) | missing(fico)
keep if origin_channel=="R"
*/
use ${analysis}figure2d,clear 

twoway(histogram fico if Type=="Bank", color(black) )(histogram fico if Type=="Sbank", color(gs8%70) lcolor(gs8%0)), ytitle("Density") xtitle("FICO") ylabel(,grid gsty(dot)) graphregion(fcolor(white) color(white) lcolor(white)) ///
		  		legend(on order(2 "Shadow Bank" 1 "Bank" ) row(2) ring(0) position(11) ) ///
				title("Borrower Fico - Ginnie",  size(medsmall) ) xlabel(,grid gsty(dot))
graph export figure2d.png, as(png) replace

// Figure 3

use ${analysis}figure3,clear

preserve
collapse overlap, by(NMLS Year match)
twoway(histogram  overlap if match==1, fraction color(black))(  histogram overlap if match==0, fraction color(gs8%70) lcolor(gs8%0)),  ytitle("Fraction") xtitle("Ratio of Banks with Overlapped Counties") graphregion(color(white) margin(5 5 5 5)) plotregion(lcolor(black))	ylabel(,nogrid) legend(order(1 "Pairs w/ Funding Relationship" 2 "Pairs w/o Funding Relationship") col(1) region(lc(black)) ring(0) position(11)) 
graph export figure3a.png, as(png) replace

restore
collapse overlap, by(rssdhcr Year match)
twoway(histogram  overlap if match==1, fraction color(black))(  histogram overlap if match==0, fraction color(gs8%70) lcolor(gs8%0)),  ytitle("Fraction") xtitle("Ratio of Shadow Banks with Overlapped Counties") graphregion(color(white) margin(5 5 5 5)) plotregion(lcolor(black))	ylabel(,nogrid) legend(order(1 "Pairs w/ Funding Relationship" 2 "Pairs w/o Funding Relationship") col(1) region(lc(black)) ring(0) position(11)) 
graph export figure3b.png, as(png) replace

// Figure 4

use ${data}Persist_Fig_Sample,clear
twoway 	(line cond_lend t, sort  mc(black) lc(black) lpattern(solid) lwidth(thick)) ///
	(line uncond_lend t, sort  mc(black) lc(black) lpattern(shortdash) lwidth(thick) ) ///
		(line cond_lend_bank t, sort  mc(black)  lc(black) lpattern(dash)) ///
		(line cond_lend_nbank t, sort  mc(black) lc(black) lpattern(longdash)), ///
    	graphregion(color(white) margin(5 5 5 5)) plotregion(lcolor(black))	///	 
		ytitle("Probability of Lending", size(medsmall)) ///
		 xlabel(,grid gsty(dot)) xtitle("Number of Quarters Since Initial Lending Period", size(medsmall)) legend(on order(1 "Average" 2 "Unconditional [Benchmark]" 3 "Bank" 4 "Other Financial Institution" ) size(small) col(1) region(lc(black)) ring(0) position(1) ) ylabel(0(0.3)1.2,grid gsty(dot))
graph export figure4.png, as(png) replace

// Figure 5 
use ${analysis}table3_int_rate,clear
drop if Year==2018 
tab Year, gen(Y)
gen high_og_2011 = 0		

gen high_og_2012 = high_og_shock*Y2		
gen high_og_2013 = high_og_shock*Y3
gen high_og_2014 = high_og_shock*Y4
gen high_og_2015 = high_og_shock*Y5
gen high_og_2016 = high_og_shock*Y6
gen high_og_2017 = high_og_shock*Y7

drop Y1 Year

xtreg int_rate_v1 high_og_20*  Y2 Y3 Y4 Y5 Y6 Y7 ,  i(NMLS) fe vce(cluster NMLS)

eststo og_rate

coefplot (og_rate, omitted keep(high_og_2012 high_og_2013 high_og_2014 high_og_2015 high_og_2016 high_og_2017) recast(connected) ciopts(recast(rcap) lcol(black) ) levels(95)   msiz(small)  mc(black)   ) /// 
          , relocate(high_og_2015 = 4 high_og_2016=5 high_og_2017=6) vertical ytitle("Difference in Funding Cost") xtitle("Baseline Year: 2011")  ylabel(-1(1)2,nogrid) graphregion(color(white) margin(5 5 5 5)) plotregion(lcolor(black)) ///
		  xlabel( 1 "2012" 2 "2013" 3 "2014" 4 "2015" 5 "2016" 6 "2017", notick) xticks(0.5(1)4.5) yline(0, lpattern(dash) lcolor(gs8))  xline(3, lcolor(gs8) lpattern(dash))	  
graph export figure5a.png, as(png) replace


use ${analysis}table3_match,clear 
drop if Year==2018 
tab Year, gen(Y)

gen high_og_2011 = 0		

gen high_og_2012 = high_og_share*Y2		
gen high_og_2013 = high_og_share*Y3
gen high_og_2014 = high_og_share*Y4
gen high_og_2015 = high_og_share*Y5
gen high_og_2016 = high_og_share*Y6
gen high_og_2017 = high_og_share*Y7

drop Y1 Year

eststo: reghdfe matched high_og_20*  Y2 Y3 Y4 Y5 Y6 Y7    , a(nmls_d rssdhcr) cluster(  NMLS rssdhcr)
eststo match

coefplot (match, omitted keep(high_og_2012 high_og_2013 high_og_2014 high_og_2015 high_og_2016 high_og_2017) recast(connected) ciopts(recast(rcap) lcol(black) ) levels(90)   msiz(small)  mc(black)   ) /// 
          , relocate(high_og_2015 = 4 high_og_2016=5 high_og_2017=6) vertical ytitle("Difference in the Matching Likelihood") xtitle("Baseline Year: 2011") ylabel(-20(10)20,nogrid) graphregion(color(white) margin(5 5 5 5)) plotregion(lcolor(black)) ///
		  xlabel( 1 "2012" 2 "2013" 3 "2014" 4 "2015" 5 "2016" 6 "2017", notick) xticks(0.5(1)4.5) yline(0, lpattern(dash) lcolor(gs8))  xline(3, lpattern(dash)lcolor(gs8))	  
graph export figure5b.png, as(png) replace

use ${analysis}table3_usage,clear 
drop if Year==2018 | Year==2011
tab Year, gen(Y)

gen high_og_2013 = 0		


gen high_og_2012 = high_og_share*Y1
gen high_og_2014 = high_og_share*Y3
gen high_og_2015 = high_og_share*Y4
gen high_og_2016 = high_og_share*Y5
gen high_og_2017 = high_og_share*Y6

drop  Year

eststo: reghdfe usage high_og_20*  Y1 Y2 Y3 Y4 Y5 Y6     , a(nmls_d link) cluster(  NMLS rssdhcr)
eststo usage

coefplot (usage, omitted keep( high_og_2012 high_og_2013 high_og_2014 high_og_2015 high_og_2016 high_og_2017) recast(connected) ciopts(recast(rcap) lcol(black) ) levels(90)   msiz(small)  mc(black)   ) /// 
          , relocate(high_og_2012 =1 high_og_2013 =2 high_og_2014 = 3 high_og_2015=4 high_og_2016=5 high_og_2017=6) vertical ytitle("Difference in Credit Line Usage (Millions)") xtitle("Baseline Year: 2013") ylabel(-50(25)25,nogrid) graphregion(color(white) margin(5 5 5 5)) plotregion(lcolor(black)) ///
		  xlabel( 1 "2012" 2 "2013" 3 "2014" 4 "2015" 5 "2016" 6 "2017", notick) xticks(0.5(1)4.5) yline(0, lpattern(dash) lcolor(gs8))  xline(3, lpattern(dash) lcolor(gs8))	  
graph export figure5c.png, as(png) replace

////////////////////////// Table 1 /////////////////////////////////////////////
use ${analysis}table1a_year,clear 
eststo clear
eststo:  estpost summarize ins_tot_loan concentration N_state,detail
esttab,  cells("count mean(fmt(2)) p50(fmt(2)) sd(fmt(2)) ") label replace 

use ${analysis}table1a_quarter,clear 
eststo clear
eststo: estpost summarize limit  t_usage   if limit<1000000 , detail
esttab,  cells("count mean(fmt(2)) p50(fmt(2)) sd(fmt(2)) ") label replace 
keep NMLS Year quarter total_asset mortgage_share debt num_line interest_rate sd_usage_share  sd_limit_share      
duplicates drop
replace total_asset = total_asset/1000000000
drop if total_asset<=0 | missing(debt)

replace interest_rate = . if interest_rate<=0 
_pctile interest_rate, nq(100)
replace interest_rate = r(r99) if interest_rate>r(r99) & !missing(interest_rate)
replace interest_rate = r(r1) if interest_rate<r(r1)
egen sd_interest = sd(interest_rate), by(NMLS)
replace sd_interest = 0 if missing(sd_interest) & !missing(interest_rate)

eststo clear
eststo:  estpost summarize total_asset mortgage_share debt num_line  sd_usage_share  sd_limit_share interest_rate sd_interest ,detail
esttab,  cells("count mean(fmt(2)) p50(fmt(2)) sd(fmt(2)) ") label replace 


use ${analysis}table1a_wb,clear 
eststo: estpost summarize hcr_asset loan_share reloan_share ,detail
esttab,  cells("count mean(fmt(2)) p50(fmt(2)) sd(fmt(2)) ") label replace 

keep Year rssdhcr ins_loan_bhc concentration N_state
duplicates drop
replace ins_loan_bhc = ins_loan_bhc/1000000
eststo: estpost summarize  ins_loan_bhc concentration N_state ,detail
egen num_bank = group(rssdhcr)
sum num_bank

*Pairwise summary stat
use ${analysis}table2_panelA,clear
replace max_limit = max_limit/1000000
eststo: estpost summarize  geo_overlap max_limit  if matched==100 ,detail
eststo: estpost summarize  geo_overlap matched,detail
esttab,  cells("count mean(fmt(2)) p50(fmt(2)) sd(fmt(2)) ") label replace 


////////////////////////// Table 2 /////////////////////////////////////////////
// Panel A
use ${analysis}table2_panelA,clear  
eststo clear
eststo: reghdfe matched ln_hq_distance geo_overlap_sd   ln_lender_asset ln_lender_origin nondep_fin_to_asset reloan_to_asset eta core_dep_share fhlb_ta , a(d_nmls) cluster(NMLS rssdhcr)
eststo: reghdfe matched ln_hq_distance geo_overlap_sd , a(d_nmls d_id) cluster(NMLS rssdhcr)
eststo: reghdfe matched  ln_hq_distance geo_overlap_sd if close==1, a(d_nmls d_id) cluster(NMLS rssdhcr)
eststo: reghdfe matched ln_hq_distance geo_overlap_sd if  borr_disp_bin==2 & lender_disp_bin>1, a(d_nmls d_id) cluster(NMLS rssdhcr)
eststo: reghdfe matched ln_hq_distance geo_overlap_sd if borr_disp_bin==1 & lender_disp_bin==1 , a(d_nmls d_id) cluster(NMLS rssdhcr)
eststo: reghdfe matched ln_hq_distance geo_overlap_sd if borr_size_bin==1 & lender_size_bin<4 , a(d_nmls d_id) cluster(NMLS rssdhcr)
eststo: reghdfe matched ln_hq_distance geo_overlap_sd if borr_size_bin==2 & lender_size_bin==4 , a(d_nmls d_id) cluster(NMLS rssdhcr)
estout, style(tex) cells(b(star fmt(2)) se(par fmt(3))) starlevels(* 0.10 ** 0.05 *** 0.010) stats( fe r2 N) label 


// Panel B
use ${analysis}table2_panelB,clear 

eststo clear
eststo: reghdfe geo_overlap_sd ln_hq_distance overlap_iv_sd , a(d_nmls d_id) cluster(NMLS rssdhcr)
eststo: ivreghdfe matched  ln_hq_distance (geo_overlap_sd = overlap_iv_sd) , a(d_nmls d_id) cluster(NMLS rssdhcr)
eststo: reghdfe matched ln_hq_distance overlap_iv_sd , a(d_nmls d_id) cluster(NMLS rssdhcr)

eststo: reghdfe geo_overlap_sd ln_hq_distance overlap_iv_sd  if close==1, a(d_nmls d_id) cluster(NMLS rssdhcr)
eststo: ivreghdfe matched  ln_hq_distance (geo_overlap_sd = overlap_iv_sd) if close==1, a(d_nmls d_id) cluster(NMLS rssdhcr)
eststo: reghdfe matched ln_hq_distance overlap_iv_sd  if close==1, a(d_nmls d_id) cluster(NMLS rssdhcr)
estout, style(tex) cells(b(star fmt(2)) se(par fmt(3))) starlevels(* 0.10 ** 0.05 *** 0.010) stats( fe r2 N) label 


////////////////////////// Table 3 /////////////////////////////////////////////
use ${analysis}table3_int_rate,clear
eststo clear
eststo: xtreg int_rate_v1 i.high_og_shock##i.post i.Year if static==1 ,  i(NMLS) fe vce(cluster NMLS)
eststo: xtreg int_rate_v1 i.high_og_shock##i.post c.ln_asset##i.post c.a_*##i.post i.Year if static==1, i(NMLS) fe vce(cluster NMLS)
esttab , keep(1.high_og_shock*)stats(N r2) se(3) b(3) star(* 0.10 ** 0.05 *** 0.01)  replace 

use ${analysis}table3_match,clear 
gen static = 1 if Year>=2012 & Year<=2016
eststo: reghdfe matched  i.high_og_share##i.post  if  static==1  , a(nmls_d) cluster(  NMLS)

use ${analysis}table3_usage,clear 
eststo: reghdfe usage  i.high_og_share##i.post  if   static==1 , a(link nmls_d) cluster(  rssdhcr NMLS)
eststo: reghdfe usage_share  i.high_og_share##i.post  if  static==1  , a(link nmls_d) cluster(  rssdhcr NMLS)
esttab , keep(1.high_og_share*) stats(N r2) se(3) b(3) star(* 0.10 ** 0.05 *** 0.01)  replace 
 
///////////////////////// Table 4 //////////////////////////////////////////////
use ${analysis}table4,clear 
eststo clear 
eststo: reghdfe sb_share_count hhi_sd ln_income ln_pop employment_rate , a(bank_t state_t) vce(cluster County rssdhcr)
eststo: reghdfe sb_share_count hhi_sd  ln_income ln_pop employment_rate if regional==1, a(bank_t state_t ) vce(cluster County rssdhcr)
eststo: reghdfe sb_share_count hhi_sd  ln_income ln_pop employment_rate if regional==0, a(bank_t state_t) vce(cluster County rssdhcr)
eststo: reghdfe sb_share_count hhi_sd  ln_income ln_pop employment_rate if small==1, a(bank_t state_t ) vce(cluster County rssdhcr)
eststo: reghdfe sb_share_count hhi_sd ln_income ln_pop employment_rate if small==0, a(bank_t state_t) vce(cluster County rssdhcr)

estout, style(tex) cells(b(star fmt(2)) se(par fmt(3))) starlevels(* 0.10 ** 0.05 *** 0.010) stats( fe r2 N) label 

//////////////////////// Table 5 ///////////////////////////////////////////////
use ${analysis}table5_loan,clear 
eststo clear 

eststo: reghdfe origin_int_rate wl_share c.ltv##i.date c.fico##i.date c.dti##i.date c.ltv2##i.date c.fico2##i.date c.dti2##i.date, a(lender_date msa_date) cluster(msa_date)
keep rssdhcr ave_mort_rate wl_share lender_date msa_date date msa 
bys rssdhcr ave_mort_rate wl_share lender_date msa_date date msa: drop if _n>1

eststo: reghdfe ave_mort_rate wl_share, a(lender_date msa_date) cluster(msa_date)
estout, style(tex) cells(b(star fmt(2)) se(par fmt(2))) starlevels(* 0.10 ** 0.05 *** 0.010) keep(wl_share) stats( r2 N) label 

